/* Copyright 2009 Requirements Management System 
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.mags.remas.model.commons.checker;

import java.io.BufferedReader;  
import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import org.mags.remas.exception.SingletonExceptionHandler;

/**
 * 
 * @author Mario Gaitan
 *
 */
class SQLColumn {
	private String name;
	private int dataType;
	private int columnSize;
	private int decimalDigits;
	private boolean nullable;
	public int getDataType() {
		return dataType;
	}
	public void setDataType(int dataType) {
		this.dataType = dataType;
	}
	public int getColumnSize() {
		return columnSize;
	}
	public void setColumnSize(int columnSize) {
		this.columnSize = columnSize;
	}
	public int getDecimalDigits() {
		return decimalDigits;
	}
	public void setDecimalDigits(int decimalDigits) {
		this.decimalDigits = decimalDigits;
	}
	public boolean isNullable() {
		return nullable;
	}
	public void setNullable(boolean nullable) {
		this.nullable = nullable;
	}
	public SQLColumn(){}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
}
class SQLTable {
	private String name;
	private Map<String, SQLColumn> columns = new HashMap<String, SQLColumn>();
	public SQLTable() {}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Map<String, SQLColumn> getColumns() {
		return columns;
	}
	public SQLColumn getColumnByName(String columnName) {
		return columns.get(columnName);
	}
	public void addColumn(SQLColumn column) {
		if(columns.get(column.getName())==null)
			columns.put(column.getName(), column);
	}
}
public class SQLUtils {
	private String lastError = "";
	private String lastKeyError = "";
	private Map<String, SQLTable> tables = new HashMap<String, SQLTable>();
	private Connection connection;
	private Throwable lastException = null;
	public boolean isMajor(int major, int minor, int micro) {
		if(getVersionMajor()<major)
			return true;
		if(getVersionMinor()<minor)
			return true;
		if(getVersionMicro()<micro)
			return true;
		return false;
	}
	
	public int getVersionMajor() {
		ResultSet resultSet = (ResultSet)executeSelect("select numeric_value from app.system_property where name = 'VERSION MAJOR'");
		try {
			if(resultSet!=null&&resultSet.next()) { 
				int v = resultSet.getInt(1);
				return v;
			}
		} catch (SQLException e) {
		}
		return 0;
	}
	public int getVersionMinor() {
		ResultSet resultSet = (ResultSet)executeSelect("select numeric_value from app.system_property where name = 'VERSION MINOR'");
		try {
			if(resultSet!=null&&resultSet.next()) {
				int v = resultSet.getInt(1);
				return v;
				
			}
		} catch (SQLException e) {
		}
		return 0;
	}
	public int getVersionMicro() {
		ResultSet resultSet = (ResultSet)executeSelect("select numeric_value from app.system_property where name = 'VERSION MICRO'");
		try {
			if(resultSet!=null&&resultSet.next()) {
				int v = resultSet.getInt(1);
				return v;				
			}
		} catch (SQLException e) {
		}
		return 0;
	}
	public Connection getConnection() {
		return connection;
	}
	public void setConnection(Connection connection) {
		this.connection = connection;
	}
	public boolean hasColumn(String tableName, String columnName) {
		return (getColumn(tableName, columnName)!=null);
	}
	public SQLColumn getColumn(String tableName, String columnName) {
		SQLTable table = getTable(tableName.toUpperCase());
		return table==null?null:table.getColumnByName(columnName.toUpperCase());
	}
	public boolean hasTable(String name) {
		return getTable(name)!=null;
	}
	public SQLTable getTable(String name) {
		return tables.get(name.toUpperCase());
	}
	private void addTable(SQLTable table) {
		if(tables.get(table.getName())==null)
			tables.put(table.getName(), table);
	}
	public String getLastKeyError() {
		return lastKeyError==null?"":lastKeyError;
	}
	public Throwable getLastException() {
		return lastException;
	}
	public String getLastError() {
		return lastError==null?"":lastError;
	}
	
	private void clearLastError() {
		lastException = null;
		lastError = "";
		lastKeyError = "";
	}
	public Map<String, SQLTable> loadMetadata(String schema) {
		return loadMetadata(schema, "%");
	}
	public Map<String, SQLTable> loadMetadata(String schema, String table) {
		return loadMetadata(null, schema, table);
	}
	public Map<String, SQLTable> loadMetadata(String catalog, String schema, String table) {
		DatabaseMetaData data;
		try {
			data = connection.getMetaData();
			ResultSet _tables = data.getTables(catalog==null?null:catalog.toUpperCase(), schema==null?null:schema.toUpperCase(), table==null?null:table.toUpperCase(), null);
			while(_tables.next()) {
				String tableName = _tables.getString("TABLE_NAME").toUpperCase();
				SQLTable _table = new SQLTable();
				_table.setName(tableName);
				ResultSet _columns = data.getColumns(catalog==null?null:catalog.toUpperCase(), schema==null?null:schema.toUpperCase(), tableName, "%");
				while(_columns.next()) {
					SQLColumn column = new SQLColumn();
					column.setName(_columns.getString("COLUMN_NAME").toUpperCase());
					column.setDataType(_columns.getInt("DATA_TYPE"));
					column.setColumnSize(_columns.getInt("COLUMN_SIZE"));
					column.setDecimalDigits(_columns.getInt("DECIMAL_DIGITS"));
					if(_columns.getInt("NULLABLE")==0)
						column.setNullable(false);
					else
						column.setNullable(true);
					_table.addColumn(column);
				}
				addTable(_table);
			}		
			return tables;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	public SQLUtils(Connection connection) {
		this.connection = connection;
	}
	private void regException(Exception e) {
		lastException = e;
		lastError = SingletonExceptionHandler.getInstance().getMessage(e);
		lastKeyError = SingletonExceptionHandler.getInstance().getKey(e);		
	}
	public boolean execute(String sql) {
		try {
			clearLastError();
			Statement statement = connection.createStatement();
			statement.execute(sql);
			return true;
		} catch(Exception e) {
			regException(e);
			return false;
		}		
	}
	public PreparedStatement getPreparedStatement(String sql) {
		try {
			clearLastError();
			return connection.prepareStatement(sql);
		} catch (Exception e) {
			regException(e);
			return null;
		}
	}
	public InputStream setFileFromResource(PreparedStatement ps, int paramIndex, String fileName) {
		try {
			clearLastError();
			StringBuffer fileData = new StringBuffer();
			BufferedReader reader = new BufferedReader(new InputStreamReader(getClass().getResourceAsStream(fileName)));
	        char[] buf = new char[1024];
	        int numRead = 0;
	        while ((numRead = reader.read(buf)) != -1) {
	            String readData = String.valueOf(buf, 0, numRead);
	            fileData.append(readData);
	            buf = new char[1024];
	        }		    
	        reader.close();
		    ByteArrayInputStream stream = new ByteArrayInputStream(fileData.toString().getBytes("UTF-8"));
		    ps.setBinaryStream(paramIndex, stream, fileData.toString().getBytes("UTF-8").length);
		    stream.close();
		    return stream;
		} catch(Exception e) {
			regException(e);
			return null;
		}
	}
	public long executeCount(String sql) {
		try {
			clearLastError();
			Statement statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery(sql);
			long count = 0;
			if(resultSet.next()) 
				count = resultSet.getLong(1);
			return count;
		} catch(Exception e) {
			regException(e);
			return -1;
		}
	}
	public boolean moveNext(ResultSet resultset) {
		try {
			clearLastError();
			return resultset.next();
		} catch (Exception e) {
			regException(e);
			return false;			
		}
	}
	public Object executeSelect(String sql) {
		try {
			clearLastError();
			Statement statement = connection.createStatement();
			return statement.executeQuery(sql);
		} catch(Exception e) {
			regException(e);
			return null;
		}
	}
	public int executeUpdate(String sql) {
		try {
			clearLastError();
			Statement statement = connection.createStatement();
			return statement.executeUpdate(sql);
		} catch(Exception e) {
			regException(e);
			return -1;
		}
	}
}
